﻿Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.IO
Imports System.Data

Partial Class WebForm_ImportVolunteer_Add
    Inherits System.Web.UI.UserControl

    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
        Me.fv.InsertItemTemplate = Me.fv.ItemTemplate
        Me.fv.EditItemTemplate = Me.fv.ItemTemplate
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'MyObj.ShowMessage(DateTime.Now)

        'Dim a As New MyStringBuilder()
        'a.AppendFormat("{0:yyyy/MM/dd}", DateTime.Now)
        'MyObj.ShowMessage(a.ToString())
    End Sub

    Protected Sub fv_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles fv.DataBound
        If Not IsPostBack Then
            Me.BindAvailableWorkPeriod()
            Me.BindDDLOrganizationID()
        End If
    End Sub

    Protected Sub fv_ItemCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewCommandEventArgs) Handles fv.ItemCommand
        If e.CommandName = "btnUpload" Then
            If Me.SaveData() = True Then
                Me.InitData(Me.ImportID)
                RaiseEvent Saved(Me, New EventArgs)
            End If
        End If
    End Sub
    Protected Sub ddlOrganizationID_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim ddlOrganizationID As DropDownList = TryCast(Me.fv.FindControl("ddlOrganizationID"), DropDownList)
        Dim ddlVOrganizationID As DropDownList = TryCast(Me.fv.FindControl("ddlVOrganizationID"), DropDownList)

        If Me.ddlOrganizationID.SelectedValue <> "" Then
            Me.BindDDLVorganizationID()
        Else

            Me.phVOrganizationID.Visible = False
            ddlVOrganizationID.Items.Clear()
        End If
        RaiseEvent Loaded(Me, New EventArgs)
    End Sub
    Private Sub BindAvailableWorkPeriod()
        Dim dat As New clsData
        Dim sql As New MyStringBuilder

        sql.AppendFormat("select a.AvailableWorkTime,a.WorkTime as TimeCode ")
        sql.AppendFormat(" from (select '" + Me.GetLocalResourceObject("WorkTimeA") + "' as AvailableWorkTime,'A' as WorkTime,1 as Sequence ")
        sql.AppendFormat(" union select '" + Me.GetLocalResourceObject("WorkTimeN") + "' as AvailableWorkTime,'N' as WorkTime,2 as Sequence ")
        sql.AppendFormat(" union select '" + Me.GetLocalResourceObject("WorkTimeP") + "' as AvailableWorkTime,'P' as WorkTime,3 as Sequence ")
        sql.AppendFormat(" ) a ")
        sql.AppendFormat(" order by a.Sequence ")

        TryCast(Me.fv.FindControl("gvAvailableWorkPeriod"), GridView).DataSource = dat.GetData(sql)
        TryCast(Me.fv.FindControl("gvAvailableWorkPeriod"), GridView).DataBind()

    End Sub

    Private Sub BindDDLOrganizationID()

        Dim ddlOrganizationID As DropDownList = TryCast(Me.fv.FindControl("ddlOrganizationID"), DropDownList)

     

        Me.ddlOrganizationID.DataTextField = "OrganizationName"
        Me.ddlOrganizationID.DataValueField = "UOrganizationID"
        Me.ddlOrganizationID.DataSource = MyObj.GetOrganizationData(clsAuth.GetUser.VolunteerLeader)
        Me.ddlOrganizationID.DataBind()

        If Me.ddlOrganizationID.Items.Count > 1 Then
            Me.ddlOrganizationID.Items.Insert(0, MyObj.GetDDLPleaseChooseItem())
        Else
            Me.BindDDLVOrganizationID()
        End If

    End Sub
    Private Sub BindDDLVorganizationID()
        Dim ddlVOrganizationID As DropDownList = TryCast(Me.fv.FindControl("ddlVOrganizationID"), DropDownList)

        Me.ddlVOrganizationID.DataTextField = "VOrganizationName"
        Me.ddlVOrganizationID.DataValueField = "VOrganizationID"
        Me.ddlVOrganizationID.DataSource = MyObj.GetVOrganizationData(ddlOrganizationID.SelectedValue)
        Me.ddlVOrganizationID.DataBind()


        If Me.ddlVOrganizationID.Items.Count > 1 Then
            Me.ddlVOrganizationID.Items.Insert(0, MyObj.GetDDLPleaseChooseItem())
        End If

        '初始值為隱藏
        Me.phVOrganizationID.Visible = True
    End Sub

    Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then
            If TypeOf DataBinder.Eval(e.Row.DataItem, "生日") Is DBNull Then
            Else
                Dim birthday As DateTime

                If Date.TryParse(DataBinder.Eval(e.Row.DataItem, "生日"), birthday) = True Then
                    'TryCast(e.Row.FindControl("Label1"), Label).Text = String.Format("民國{0}年{1:MM月dd日}", Year(birthday), birthday)
                    TryCast(e.Row.FindControl("Label1"), Label).Text = String.Format("{0}", birthday)
                End If

            End If

            If TypeOf DataBinder.Eval(e.Row.DataItem, "Deleted") Is DBNull OrElse DataBinder.Eval(e.Row.DataItem, "Deleted") = "N" Then
                TryCast(e.Row.FindControl("mvDelete"), MultiView).SetActiveView(TryCast(e.Row.FindControl("vwDelete"), View))
            Else
                TryCast(e.Row.FindControl("mvDelete"), MultiView).SetActiveView(TryCast(e.Row.FindControl("vwDeleted"), View))
            End If
        End If
    End Sub

    Protected Sub GridView1_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles GridView1.RowDeleting
        If Me.DeleteData(e.RowIndex) = True Then
            Me.InitData(Me.ImportID)
        End If
    End Sub
End Class

Partial Class WebForm_ImportVolunteer_Add
    Public Sub InitData()
        Select Case Me.DataAccessMode
            Case My.DataAccessMode.Insert
                Me.fv.ChangeMode(FormViewMode.Insert)
        End Select
    End Sub

    Public Function DeleteData(ByVal rowIndex As Integer) As Boolean
        Dim dat As New clsData
        Dim sql As New MyStringBuilder
        Dim dt As Data.DataTable
        Dim row As GridViewRow = Me.GridView1.Rows(rowIndex)

        If clsUser.CanDeleteVolunteer(TryCast(row.FindControl("txtVolunteerID"), Label).Text) = False Then
            MyObj.ShowMessage("{0} 有多項報名紀錄，無法刪除", TryCast(row.FindControl("txtVolunteerID"), Label).Text)
            Return False
        End If

        Using scope As New Transactions.TransactionScope

            sql.Length = 0
            sql.AppendFormat("Delete from Volunteer ")
            sql.AppendFormat("Where VolunteerID = '{0}'; ", TryCast(row.FindControl("txtVolunteerID"), Label).Text)
            'dat.AccData(sql)

            'sql.Length = 0
            sql.AppendFormat("Delete from VOrganizationMapping ")
            sql.AppendFormat("Where VolunteerID = '{0}'; ", TryCast(row.FindControl("txtVolunteerID"), Label).Text)
            'dat.AccData(sql)

            'sql.Length = 0
            sql.AppendFormat("update ImportVolunteerRecord ")
            sql.AppendFormat("set Deleted = 'Y' ")
            sql.AppendFormat("Where VolunteerID = '{0}' ", TryCast(row.FindControl("txtVolunteerID"), Label).Text)
            sql.AppendFormat("and ImportID = {0}; ", Me.ImportID)
            dat.AccData(sql)

            scope.Complete()
        End Using

        Return True
    End Function

    Public Sub InitData(ByVal ImportID As String)
        Dim dat As New clsData
        Dim sql As New MyStringBuilder
        Dim dt As Data.DataTable

        Me.ImportID = ImportID

        sql.AppendFormat("select a.VolunteerID as '身份證號',a.VolunteerName as '姓名',Cast(Convert(varchar(10),a.Birthday,111) as datetime) as '生日',a.MobilePhone as '行動電話'")
        sql.AppendFormat(" ,a.OfficePhone as '聯絡電話',c.RegionName+b.RegionName as '區與里',a.Address as '通訊住址',a.Email, a.PersonName as '民間團體','' as '檢查結果' ")
        sql.AppendFormat(",a.Deleted ")
        sql.AppendFormat("from ImportVolunteerRecord a")
        sql.AppendFormat(" Left join Region b on a.VillageID=b.RegionID and b.RegionType='V'")
        sql.AppendFormat(" Left join Region c on b.Parent=c.RegionID and c.RegionType='T'")
        sql.AppendFormat(" where a.ImportID='" & ImportID & "'")

        dt = dat.GetData(sql)

        GridView1.Visible = True
        GridView1.DataSource = dt
        GridView1.DataBind()
        GridView1.Columns(GridView1.Columns.Count - 2).Visible = False
        GridView1.Columns(GridView1.Columns.Count - 1).Visible = True

    End Sub

    Private Function SaveData() As Boolean
        Dim fileUpload1 As FileUpload = TryCast(Me.fv.FindControl("FileUpload1"), FileUpload)

        If Not fileUpload1.HasFile Then
            MyObj.ShowMessage("請選擇上傳檔案")
            Return False
        ElseIf fileUpload1.PostedFile.ContentType.ToString() <> "application/vnd.ms-excel" AndAlso fileUpload1.PostedFile.ContentType.ToString() <> "application/octet-stream" Then
            MyObj.ShowMessage("匯入失敗!!請選擇副檔名(xls、csv)檔案!!")
            Return False
        End If

        '上傳暫存檔
        Dim clsRandom As New clsRandom()
        Dim newName As String = Server.MapPath("~/") & "/tmp/" & clsRandom.Generate()
        fileUpload1.PostedFile.SaveAs(newName)

        Dim ExcelImportCheck As New ExcelImportCheck()
        Dim dt As New DataTable

        '檢查工作表名稱
        If ExcelImportCheck.LoadData(dt, newName, "志工名單") = False Then
            Return False
        End If

        dt.Columns.Add("Deleted")

        '檢查欄位是否都存在
        'Dim ColumnList As String(,) = {{"身份證號", "N"}, {"姓名", "N"}, {"性別", "N"}, {"生日", "N"}, {"行動電話", "N"}, {"聯絡電話", "N"}, {"鄉鎮市與村里", "N"}, {"通訊住址", "N"}, {"Email", "N"}, {"已完成基礎訓練時數", "N"}, {"已完成特殊訓練時數", "N"}, {"已完成在職訓練時數", "N"}, {"累積已服務時數", "N"}, {"教育程度", "N"}, {"是否已屆退休年齡", "N"}, {"目前或退休前服務於", "N"}, {"緊急聯絡人", "N"}, {"緊急聯絡電話", "N"}}
        Dim ColumnList As String(,) = {{"身份證號", "N"}, {"姓名", "N"}, {"生日", "N"}, {"行動電話", "N"}, {"聯絡電話", "N"}, {"區與里", "N"}, {"通訊住址", "N"}, {"Email", "N"}, {"教育程度", "N"}, {"是否已屆退休年齡", "N"}, {"目前或退休前服務於", "N"}, {"緊急聯絡人", "N"}, {"緊急聯絡電話", "N"}, {"民間團體", "N"}}
        If ExcelImportCheck.CheckColumn(dt, ColumnList) = False Then
            Return False
        End If

        '去掉空值的資料 2010/05/14 Add BY Angus
        Dim FilterValue As String = ""
        For k As Integer = 0 To (ColumnList.Length / 2) - 1
            If FilterValue = "" Then
                FilterValue &= ColumnList(k, 0) & "<>''"
            Else
                FilterValue &= " or " & ColumnList(k, 0) & "<>''"
            End If
        Next
        Dim MyDefaultView As Data.DataView = dt.DefaultView
        MyDefaultView.RowFilter = FilterValue
        dt = MyDefaultView.ToTable()

        '取出村里資料
        '==============================================
        Dim dat As New clsData
        Dim sql As New MyStringBuilder
        Dim VillageDT, TempDT As Data.DataTable
        Dim TempDataView As DataView

        sql.AppendFormat("select a.RegionID,b.RegionName+a.RegionName as Name from Region a")
        sql.AppendFormat(" join Region b on a.Parent=b.RegionID")
        sql.AppendFormat(" and b.RegionType='T'")
        sql.AppendFormat(" where a.RegionType='V'")
        VillageDT = dat.GetData(sql)
        '==============================================

        '增加[村里ID]、[檢查結果]欄位
        Dim colString As DataColumn = New DataColumn("檢查結果")
        colString.DataType = System.Type.GetType("System.String")
        dt.Columns.Add(colString)
        colString = New DataColumn("區里ID")
        colString.DataType = System.Type.GetType("System.String")
        dt.Columns.Add(colString)

        dt.Columns.Add("提示")

        dt.Columns.Add("EducationLevel")
        dt.Columns.Add("OccupationType")
        dt.Columns.Add("Retired")
        dt.Columns.Add("生日d", Type.GetType("System.DateTime"))
        dt.Columns.Add("Duplicate")

        dt.Columns.Add("已完成基礎訓練時數")
        dt.Columns.Add("已完成特殊訓練時數")
        dt.Columns.Add("已完成在職訓練時數")
        dt.Columns.Add("累積已服務時數")

        '開始逐步檢查
        Dim EndString As String
        Dim MyDate As New My.MyDate
        Dim ErrorCount As Integer = 0
        Dim skipBlank As Boolean = False
        Dim skipRow As Integer
        Dim i As Integer

        For i = 0 To dt.Rows.Count - 1
            dt.Rows(i)("檢查結果") = ""
            dt.Rows(i)("提示") = ""
            dt.Rows(i)("Duplicate") = "N"
            If dt.Rows(i)("身份證號").ToString().Trim() <> "" Then
              
                If skipBlank = False Then
                    skipRow = i
                    skipBlank = True

                End If
                Dim checkResult As Boolean = My.CheckSame.CheckSent("Volunteer", "VolunteerID", "", "VolunteerID", dt.Rows(i)("身份證號").ToString())

                If My.CheckSame.CheckSent("Volunteer", "VolunteerID", "", "VolunteerID", dt.Rows(i)("身份證號").ToString()) Then
                    dt.Rows(i)("提示") = "身份證號已存在,"
                    dt.Rows(i)("Duplicate") = "Y"
                End If

                '檢查匯入檔身份證號是否有重覆
                Dim dv As New DataView(dt)
                dv.RowFilter = String.Format("[身份證號] = '{0}' ", dt.Rows(i)("身份證號"))

                If dv.Count > 1 Then
                    dt.Rows(i)("檢查結果") &= "身份證號在匯入檔重覆,"
                End If

                Dim IDNValidator As New MyIDNValidator.IDNValidator

                If dt.Rows(i)("身份證號").ToString().Length <> 10 Then
                    dt.Rows(i)("檢查結果") &= "身份證號長度不對,"
                ElseIf IDNValidator.Validate(dt.Rows(i)("身份證號").ToString()) = False Then
                    dt.Rows(i)("檢查結果") &= "身份證號格式有誤,"
                    'Else
                    '    If skipBlank = True Then
                    '        'dt.Rows(i)("檢查結果") = "身份證字號未填寫"
                    '        If i > 0 Then
                    '            dt.Rows(i - 1)("檢查結果") = "身份證字號未填寫"
                    '        End If
                    '        Exit For
                    '    End If
                End If
            Else
                dt.Rows(i)("檢查結果") &= "未填身份證號,"

            End If

       

            If (dt.Rows(i)("姓名").ToString().Trim()) = "" Then
                dt.Rows(i)("檢查結果") &= "未填姓名,"
            ElseIf dt.Rows(i)("姓名").ToString().Length > 50 Then
                dt.Rows(i)("檢查結果") &= "姓名欄位限制(50),"
            End If

            'If (dt.Rows(i)("性別").ToString().Trim()) = "" Then
            '    dt.Rows(i)("檢查結果") &= "未填性別,"
            'Else
            '    If dt.Rows(i)("性別").ToString().Length > 1 Then
            '        dt.Rows(i)("檢查結果") &= "性別欄位限制(1),"
            '    ElseIf dt.Rows(i)("性別").ToString() <> "男" And dt.Rows(i)("性別").ToString() <> "女" Then
            '        dt.Rows(i)("檢查結果") &= "性別格式錯誤(男/女),"
            '    End If
            'End If

            If (dt.Rows(i)("生日").ToString().Trim()) = "" Then
                dt.Rows(i)("檢查結果") &= "未填生日,"
            Else

                Dim d As Date

                If Date.TryParse(dt.Rows(i)("生日").ToString().Replace(".", "/"), d) = False Then
                    dt.Rows(i)("檢查結果") &= "生日格式不正確(格式是yy.mm.dd,yy爲民國年),"
                Else
                    dt.Rows(i)("生日d") = d
                    '檢查生日是否大於今年
                    If Int32.Parse(dt.Rows(i)("生日d").ToString.Split(".")(0)) > Int32.Parse(DateTime.Now.Year - 1911) Then
                        dt.Rows(i)("檢查結果") &= "生日年份不可大於今年(格式是yy.mm.dd,yy爲民國年),"
                    End If

                End If


              
                'If (Not My.MyDate.IsDate(dt.Rows(i)("生日").ToString())) Then
                '    dt.Rows(i)("檢查結果") &= "生日格式不正確,"
                'Else
                '    dt.Rows(i)("生日") = DateTime.Parse(dt.Rows(i)("生日")).Date
                'End If
                End If

                'If (dt.Rows(i)("行動電話").ToString().Trim()) = "" Then
                '    dt.Rows(i)("檢查結果") &= "未填行動電話,"
                If dt.Rows(i)("行動電話").ToString().Length > 50 Then
                    dt.Rows(i)("檢查結果") &= "行動電話欄位限制(50),"
                End If

                If dt.Rows(i)("民間團體").ToString().Length > 50 Then
                    dt.Rows(i)("檢查結果") &= "民間團體欄位限制(50),"
                End If

                If (dt.Rows(i)("聯絡電話").ToString().Trim()) = "" Then
                    dt.Rows(i)("檢查結果") &= "未填聯絡電話,"
                ElseIf dt.Rows(i)("聯絡電話").ToString().Length > 50 Then
                    dt.Rows(i)("檢查結果") &= "聯絡電話欄位限制(50),"
                End If

                '檢查村里
                If (dt.Rows(i)("區與里").ToString().Trim()) = "" Then
                    dt.Rows(i)("檢查結果") &= "未填區與里,"
                Else
                    TempDataView = VillageDT.DefaultView
                    TempDataView.RowFilter = "Name='" + dt.Rows(i)("區與里").ToString().Trim() + "'"
                    TempDT = TempDataView.ToTable

                    If TempDT.Rows.Count <= 0 Then
                        dt.Rows(i)("檢查結果") &= "區與里不完整,"
                    Else
                        dt.Rows(i)("區里ID") = TempDT.Rows(0)("RegionID")
                    End If
                End If

                dt.Rows(i)("已完成基礎訓練時數") = 0
                dt.Rows(i)("已完成特殊訓練時數") = 0
                dt.Rows(i)("已完成在職訓練時數") = 0
                dt.Rows(i)("累積已服務時數") = 0

                'If Not Regex.IsMatch(dt.Rows(i)("已完成基礎訓練時數").ToString(), "^[0-9]*$") And dt.Rows(i)("已完成基礎訓練時數").ToString().Trim() <> "" Then
                '    dt.Rows(i)("檢查結果") &= "已完成基礎訓練時數必須為大於或等於0的整數,"
                'ElseIf dt.Rows(i)("已完成基礎訓練時數").ToString().Trim() = "" Then
                '    dt.Rows(i)("已完成基礎訓練時數") = 0
                'End If

                'If Not Regex.IsMatch(dt.Rows(i)("已完成特殊訓練時數").ToString(), "^[0-9]*$") And dt.Rows(i)("已完成特殊訓練時數").ToString().Trim() <> "" Then
                '    dt.Rows(i)("檢查結果") &= "已完成特殊訓練時數必須為大於或等於0的整數,"
                'ElseIf dt.Rows(i)("已完成特殊訓練時數").ToString().Trim() = "" Then
                '    dt.Rows(i)("已完成特殊訓練時數") = 0
                'End If

                'If Not Regex.IsMatch(dt.Rows(i)("已完成在職訓練時數").ToString(), "^[0-9]*$") And dt.Rows(i)("已完成在職訓練時數").ToString().Trim() <> "" Then
                '    dt.Rows(i)("檢查結果") &= "已完成在職訓練時數必須為大於或等於0的整數,"
                'ElseIf dt.Rows(i)("已完成在職訓練時數").ToString().Trim() = "" Then
                '    dt.Rows(i)("已完成在職訓練時數") = 0
                'End If

                'If Not Regex.IsMatch(dt.Rows(i)("累積已服務時數").ToString(), "(\d+)(((\.)(\d)+))?") And dt.Rows(i)("累積已服務時數").ToString().Trim() <> "" Then
                '    dt.Rows(i)("檢查結果") &= "累積已服務時數必須為大於0的數字,"
                'ElseIf dt.Rows(i)("累積已服務時數").ToString().Trim() = "" Then
                '    dt.Rows(i)("累積已服務時數") = 0
                'End If

                If (dt.Rows(i)("教育程度").ToString().Trim()) = "" Then
                    dt.Rows(i)("檢查結果") &= "未填教育程度,"
                Else

                    Dim eduDT As Data.DataTable

                    If eduDT Is Nothing Then
                        eduDT = MyObj.GetCodeMappingData("Volunteer", "EducationLevel")
                    End If

                    eduDT.DefaultView.RowFilter = String.Format("Text='{0}'", dt.Rows(i)("教育程度"))

                    If eduDT.DefaultView.Count = 0 Then
                        dt.Rows(i)("檢查結果") &= "教育程度有誤,"
                    Else
                        dt.Rows(i)("EducationLevel") = eduDT.DefaultView(0)("Value")
                    End If
                End If

                If (dt.Rows(i)("是否已屆退休年齡").ToString().Trim()) = "" Then
                    dt.Rows(i)("檢查結果") &= "未填是否已屆退休年齡,"
                Else

                    Dim eduDT As Data.DataTable

                    If eduDT Is Nothing Then
                        eduDT = MyObj.GetCodeMappingData("Volunteer", "Retired")
                    End If

                    eduDT.DefaultView.RowFilter = String.Format("Text='{0}'", dt.Rows(i)("是否已屆退休年齡"))

                    If eduDT.DefaultView.Count = 0 Then
                        dt.Rows(i)("檢查結果") &= "是否已屆退休年齡有誤,"
                    Else
                        dt.Rows(i)("Retired") = eduDT.DefaultView(0)("Value")
                    End If
                End If

                If (dt.Rows(i)("目前或退休前服務於").ToString().Trim()) = "" Then
                    dt.Rows(i)("檢查結果") &= "未填目前或退休前服務於,"
                Else

                    Dim eduDT As Data.DataTable

                    If eduDT Is Nothing Then
                        eduDT = MyObj.GetCodeMappingData("Volunteer", "OccupationType")
                    End If

                    eduDT.DefaultView.RowFilter = String.Format("Text='{0}'", dt.Rows(i)("目前或退休前服務於"))

                    If eduDT.DefaultView.Count = 0 Then
                        dt.Rows(i)("檢查結果") &= "目前或退休前服務於有誤,"
                    Else
                        dt.Rows(i)("OccupationType") = eduDT.DefaultView(0)("Value")
                    End If
                End If

                If (dt.Rows(i)("緊急聯絡人").ToString().Trim()) = "" Then
                    dt.Rows(i)("檢查結果") &= "未填緊急聯絡人,"
                End If

                If (dt.Rows(i)("緊急聯絡電話").ToString().Trim()) = "" Then
                    dt.Rows(i)("檢查結果") &= "未填緊急聯絡電話,"
                End If

                If (dt.Rows(i)("通訊住址").ToString().Trim()) = "" Then
                    dt.Rows(i)("檢查結果") &= "未填通訊住址,"
                End If

                EndString = dt.Rows(i)("檢查結果").ToString()
                If EndString <> "" Then
                    dt.Rows(i)("檢查結果") = EndString.Substring(0, EndString.Length - 1) & "."

                    ErrorCount += 1
                Else
                    dt.Rows(i)("檢查結果") = "正常"
                End If

                If dt.Rows(i)("Duplicate") = "N" Then
                    dt.Rows(i)("檢查結果") = dt.Rows(i)("提示") + dt.Rows(i)("檢查結果")
                End If
        Next

        '有異常則不寫入資料
        If (ErrorCount > 0) Then
            GridView1.Columns(GridView1.Columns.Count - 2).Visible = True
            GridView1.Columns(GridView1.Columns.Count - 1).Visible = False
            GridView1.Visible = True
            GridView1.DataSource = dt
            GridView1.DataBind()
            MyObj.ShowMessage("匯入失敗!!" & ErrorCount.ToString() & "筆志工資料異常!!")
            Return False
        Else
            Using scope As New Transactions.TransactionScope

                sql.Length = 0
                sql.AppendFormat("insert ImportVolunteerHeader(CreateDate,CreateBy) select ")
                sql.AppendFormat("{0}", "getdate()")
                sql.AppendFormat(",'{0}'", clsAuth.GetUserID())

                dat.AccData(sql)

                Dim id As Integer = MyObj.GetIdentity()

                Dim basicTrainingHour As Integer = MyObj.GetParameter("VBook", "BasicTrainingHour")
                Dim specialTrainingHour As Integer = MyObj.GetParameter("VBook", "SpecialTrainingHour")
                Dim workTrainingHour As Integer = MyObj.GetParameter("VBook", "WorkTrainingHour")

                Dim insertCount As Integer

                If skipRow > 0 Then
                    insertCount = skipRow
                Else
                    insertCount = dt.Rows.Count
                End If

                For i = 0 To insertCount - 1
                    If dt.Rows(i)("Duplicate") = "Y" Then
                        '更新志工資料

                        Dim password As String = Right(dt.Rows(i)("身份證號").ToString(), 2) + Convert.ToDateTime(dt.Rows(i)("生日d")).ToString("MMdd")
                        sql = New MyStringBuilder
                        sql.Length = 0
                        sql.AppendFormat("update Volunteer set ")
                        sql.AppendFormat("VolunteerName=N'{0}' ", LTrim(dt.Rows(i)("姓名").ToString().Replace("  ", "").Replace(" ", "")))
                        sql.AppendFormat(",Password='{0}' ", clsAuth.EncryptPassword(password))
                        sql.AppendFormat(",IdentificationID='{0}' ", dt.Rows(i)("身份證號").ToString())
                        sql.AppendFormat(",IdentificationType={0} ", "1")
                        sql.AppendFormat(",Gender='{0}' ", IIf(dt.Rows(i)("身份證號").ToString().Substring(1, 1) = "1", "M", "F"))
                        sql.AppendFormat(",Birthday='{0:yyyy/MM/dd}' ", dt.Rows(i)("生日d"))
                        sql.AppendFormat(",MobilePhone='{0}' ", dt.Rows(i)("行動電話").ToString())
                        sql.AppendFormat(",OfficePhone='{0}' ", dt.Rows(i)("聯絡電話").ToString())
                        sql.AppendFormat(",BirthVillageID='{0}' ", dt.Rows(i)("區里ID").ToString())
                        sql.AppendFormat(",BirthAddress='{0}' ", dt.Rows(i)("通訊住址").ToString())
                        sql.AppendFormat(",VillageID='{0}' ", dt.Rows(i)("區里ID").ToString())
                        sql.AppendFormat(",Address='{0}' ", dt.Rows(i)("通訊住址").ToString())
                        sql.AppendFormat(",PersonName='{0}' ", dt.Rows(i)("民間團體").ToString())
                        sql.AppendFormat(",EmergentPerson='{0}' ", dt.Rows(i)("緊急聯絡人").ToString())
                        sql.AppendFormat(",EmergentPhone='{0}' ", dt.Rows(i)("緊急聯絡電話").ToString())
                        sql.AppendFormat(",Email='{0}' ", dt.Rows(i)("Email").ToString())
                        sql.AppendFormat(",EducationLevel='{0}' ", dt.Rows(i)("EducationLevel").ToString())
                        sql.AppendFormat(",OccupationType='{0}' ", dt.Rows(i)("OccupationType").ToString())
                        sql.AppendFormat(",Retired='{0}' ", dt.Rows(i)("Retired").ToString())
                        sql.AppendFormat(", UpdateDate = getdate() ")
                        sql.AppendFormat(", UpdateBy = '{0}' ", clsAuth.GetUserID())
                        sql.AppendFormat("where VolunteerID ='{0}'; ", dt.Rows(i)("身份證號").ToString().Trim())

                        sql.AppendFormat("delete from VOrganizationMapping ")
                        sql.AppendFormat("where VolunteerID ='{0}'; ", dt.Rows(i)("身份證號").ToString().Trim())


                        dat.AccData(sql)
                    Else

                        sql = New MyStringBuilder
                        sql.AppendFormat("insert ImportVolunteerRecord(ImportID,VolunteerID,VolunteerName,Gender,IdentificationID,Birthday,MobilePhone,OfficePhone,VillageID,Address,Email,BasicTrainingHour,SpecialTrainingHour,WorkTrainingHour,WorkHour,Deleted,PersonName) select ")
                        sql.AppendFormat(" {0}", id)
                        sql.AppendFormat(",'{0}'", dt.Rows(i)("身份證號").ToString().Trim())
                        sql.AppendFormat(",N'{0}'", LTrim(dt.Rows(i)("姓名").ToString().Replace("  ", "").Replace(" ", "")))
                        sql.AppendFormat(",'{0}'", IIf(dt.Rows(i)("身份證號").ToString().Substring(1, 1) = "1", "M", "F"))
                        sql.AppendFormat(",'{0}'", dt.Rows(i)("身份證號").ToString().Trim())
                        sql.AppendFormat(",'{0:yyyy/MM/dd}'", dt.Rows(i)("生日d"))
                        sql.AppendFormat(",'{0}'", dt.Rows(i)("行動電話").ToString())
                        sql.AppendFormat(",'{0}'", dt.Rows(i)("聯絡電話").ToString())
                        sql.AppendFormat(",'{0}'", dt.Rows(i)("區里ID").ToString())
                        sql.AppendFormat(",'{0}'", dt.Rows(i)("通訊住址").ToString())
                        sql.AppendFormat(",'{0}'", dt.Rows(i)("Email").ToString())
                        sql.AppendFormat(",{0}", dt.Rows(i)("已完成基礎訓練時數"))
                        sql.AppendFormat(",{0}", dt.Rows(i)("已完成特殊訓練時數"))
                        sql.AppendFormat(",{0}", dt.Rows(i)("已完成在職訓練時數"))
                        sql.AppendFormat(",{0}", dt.Rows(i)("累積已服務時數"))
                        sql.AppendFormat(",'{0}'", "N")
                        sql.AppendFormat(",'{0}'; ", dt.Rows(i)("民間團體").ToString())

                        'dat.AccData(sql)

                        '更新志工資料
                        ' sql.Length = 0
                        sql.AppendFormat("insert Volunteer (VolunteerID,VolunteerName,Password,IdentificationID,IdentificationType,Active,RegisterDate,Gender,Birthday,MobilePhone,OfficePhone,BirthVillageID,BirthAddress,VillageID,Address,EmergentPerson,EmergentPhone,Email,CreateDate,CreateBy,EducationLevel,OccupationType,Retired,PersonName) select ")
                        sql.AppendFormat(" '{0}' ", dt.Rows(i)("身份證號").ToString().Trim())
                        sql.AppendFormat(",N'{0}' ", LTrim(dt.Rows(i)("姓名").ToString().Replace("  ", "").Replace(" ", "")))
                        'sql.AppendFormat(",'{0}' ", Right(dt.Rows(i)("身份證號").ToString(), 2) & Convert.ToDateTime(dt.Rows(i)("生日")).ToString("MMdd"))

                        Dim password As String = Right(dt.Rows(i)("身份證號").ToString(), 2) + Convert.ToDateTime(dt.Rows(i)("生日d")).ToString("MMdd")
                        sql.AppendFormat(",'{0}' ", clsAuth.EncryptPassword(password))
                        sql.AppendFormat(",'{0}' ", dt.Rows(i)("身份證號").ToString())
                        sql.AppendFormat(",'{0}' ", "1")

                        '                If dt.Rows(i)("已完成基礎訓練時數") >= basicTrainingHour _
                        'And dt.Rows(i)("已完成特殊訓練時數") >= specialTrainingHour _
                        'And dt.Rows(i)("已完成在職訓練時數") >= workTrainingHour Then
                        '                    sql.AppendFormat(",'{0}' ", My.VolunteerStatus.VBooked)
                        '                Else
                        '                    sql.AppendFormat(",'{0}' ", "2") 'Active
                        '                End If

                        sql.AppendFormat(",'{0}' ", "2") 'Active

                        sql.AppendFormat(",{0} ", "getdate()")
                        sql.AppendFormat(",'{0}' ", IIf(dt.Rows(i)("身份證號").ToString().Substring(1, 1) = "1", "M", "F"))
                        sql.AppendFormat(",'{0:yyyy/MM/dd}' ", dt.Rows(i)("生日d"))
                        sql.AppendFormat(",'{0}' ", dt.Rows(i)("行動電話").ToString())
                        sql.AppendFormat(",'{0}' ", dt.Rows(i)("聯絡電話").ToString())
                        sql.AppendFormat(",'{0}' ", dt.Rows(i)("區里ID").ToString())
                        sql.AppendFormat(",'{0}' ", dt.Rows(i)("通訊住址").ToString())
                        sql.AppendFormat(",'{0}' ", dt.Rows(i)("區里ID").ToString())
                        sql.AppendFormat(",'{0}' ", dt.Rows(i)("通訊住址").ToString())
                        sql.AppendFormat(",'{0}' ", dt.Rows(i)("緊急聯絡人").ToString())
                        sql.AppendFormat(",'{0}' ", dt.Rows(i)("緊急聯絡電話").ToString())
                        sql.AppendFormat(",'{0}' ", dt.Rows(i)("Email").ToString())
                        sql.AppendFormat(",{0}", "getdate()")
                        sql.AppendFormat(",'{0}'", clsAuth.GetUserID())
                        sql.AppendFormat(",'{0}' ", dt.Rows(i)("EducationLevel").ToString())
                        sql.AppendFormat(",'{0}' ", dt.Rows(i)("OccupationType").ToString())
                        sql.AppendFormat(",'{0}' ", dt.Rows(i)("Retired").ToString())
                        sql.AppendFormat(",'{0}'; ", dt.Rows(i)("民間團體").ToString())

                        '     dat.AccData(sql)

                        '可服務時間
                        'sql.Length = 0
                        sql.AppendFormat("delete from VolunteerWorkTime where ")
                        sql.AppendFormat(" VolunteerID='{0}'; ", dt.Rows(i)("身份證號").ToString().Trim())
                        For j As Integer = 0 To 2
                            sql.AppendFormat("insert VolunteerWorkTime select ")
                            sql.AppendFormat(" '{0}' ", dt.Rows(i)("身份證號").ToString().Trim())
                            Select Case j
                                Case 0
                                    sql.AppendFormat(",'{0}' ", "A")
                                Case 1
                                    sql.AppendFormat(",'{0}' ", "N")
                                Case 2
                                    sql.AppendFormat(",'{0}' ", "P")
                                Case Else
                                    sql.AppendFormat(",'{0}' ", "A")
                            End Select
                            sql.AppendFormat(",'{0}' ", Me.WorkTime(j, 0))
                            sql.AppendFormat(",'{0}' ", Me.WorkTime(j, 1))
                            sql.AppendFormat(",'{0}' ", Me.WorkTime(j, 2))
                            sql.AppendFormat(",'{0}' ", Me.WorkTime(j, 3))
                            sql.AppendFormat(",'{0}' ", Me.WorkTime(j, 4))
                            sql.AppendFormat(",'{0}' ", Me.WorkTime(j, 5))
                            sql.AppendFormat(",'{0}'; ", Me.WorkTime(j, 6))
                        Next
                        'dat.AccData(sql)

                        '訓練時數
                        'sql.Length = 0
                        sql.AppendFormat("delete from OtherTrainingRecord where ")
                        sql.AppendFormat(" VolunteerID='{0}'; ", dt.Rows(i)("身份證號").ToString().Trim())
                        For j As Integer = 0 To 2
                            sql.AppendFormat("insert OtherTrainingRecord (VolunteerID,TrainingHour,TrainingType,CreateBy,CreateDate,Active) select ")
                            sql.AppendFormat(" '{0}' ", dt.Rows(i)("身份證號").ToString().Trim())
                            Select Case j
                                Case 0
                                    sql.AppendFormat(",'{0}' ", dt.Rows(i)("已完成基礎訓練時數").ToString())
                                    sql.AppendFormat(",'{0}' ", "B")
                                Case 1
                                    sql.AppendFormat(",'{0}' ", dt.Rows(i)("已完成特殊訓練時數").ToString())
                                    sql.AppendFormat(",'{0}' ", "S")
                                Case 2
                                    sql.AppendFormat(",'{0}' ", dt.Rows(i)("已完成在職訓練時數").ToString())
                                    sql.AppendFormat(",'{0}' ", "W")
                            End Select
                            sql.AppendFormat(",'{0}' ", dt.Rows(i)("身份證號").ToString().Trim())
                            sql.AppendFormat(",{0} ", "getdate()")
                            sql.AppendFormat(",'Y'; ")
                        Next
                        'dat.AccData(sql)

                        '訓練時數
                        '   sql.Length = 0
                        sql.AppendFormat("delete from OtherWorkRecord where ")
                        sql.AppendFormat(" VolunteerID='{0}'; ", dt.Rows(i)("身份證號").ToString().Trim())
                        sql.AppendFormat("insert OtherWorkRecord (VolunteerID,WorkHour,WorkTimes,CreateDate,CreateBy,Active) select ")
                        sql.AppendFormat(" '{0}' ", dt.Rows(i)("身份證號").ToString().Trim())
                        sql.AppendFormat(",{0} ", dt.Rows(i)("累積已服務時數").ToString())
                        sql.AppendFormat(",1")
                        sql.AppendFormat(",{0} ", "getdate()")
                        sql.AppendFormat(",'{0}' ", dt.Rows(i)("身份證號").ToString().Trim())
                        sql.AppendFormat(",'Y'; ")
                        dat.AccData(sql)

                        '棣屬志工隊
                        'sql.Length = 0
                        'sql.AppendFormat("delete from VOrganizationMapping where ")
                        'sql.AppendFormat(" VolunteerID='{0}' ", dt.Rows(i)("身份證號").ToString())
                        'sql.AppendFormat("and VOrganizationID='{0}' ", TryCast(fv.FindControl("ddlVOrganizationID"), DropDownList).SelectedValue)

                        'If TryCast(fv.FindControl("ddlVOrganizationID"), DropDownList).SelectedValue <> "0" Then
                        '    sql.AppendFormat("insert VOrganizationMapping (VolunteerID,VOrganizationID,EffectDate) select ")
                        '    sql.AppendFormat(" '{0}' ", dt.Rows(i)("身份證號").ToString())
                        '    sql.AppendFormat(",'{0}' ", TryCast(fv.FindControl("ddlVOrganizationID"), DropDownList).SelectedValue)
                        '    sql.AppendFormat(",'{0}'", "9999/12/31")
                        'End If

                        'dat.AccData(sql)

                    End If

                    '自動加入該志工隊
                    ' If TryCast(fv.FindControl("ddlVOrganizationID"), DropDownList).SelectedValue <> "0" Then
                    sql.Length = 0
                    sql.AppendFormat("insert VOrganizationMapping (VolunteerID,VOrganizationID,EffectDate) select ")
                    sql.AppendFormat(" '{0}' ", dt.Rows(i)("身份證號").ToString().Trim())
                    sql.AppendFormat(",'{0}' ", TryCast(fv.FindControl("ddlVOrganizationID"), DropDownList).SelectedValue)
                    sql.AppendFormat(",'{0}' ", "9999/12/31")
                    sql.AppendFormat("where not exists( ")
                    sql.AppendFormat("select * from VOrganizationMapping where ")
                    sql.AppendFormat("VolunteerID='{0}' ", dt.Rows(i)("身份證號").ToString().Trim())
                    sql.AppendFormat("and VOrganizationID<>0 ")
                    sql.AppendFormat(") ")
                    dat.AccData(sql)
                    ' End If
                Next
                scope.Complete()

                GridView1.Visible = False
                MyObj.ShowMessage("匯入成功!!共匯入" & insertCount & "筆資料!!")

                Me.ImportID = id
            End Using
        End If

        Return True
    End Function

    Private Property ImportID() As Integer
        Get
            Return ViewState("ImportID")
        End Get
        Set(ByVal value As Integer)
            ViewState("ImportID") = value
        End Set
    End Property

    Public Property DataAccessMode() As My.DataAccessMode
        Get
            If ViewState("DataAccessMode") Is Nothing Then
                Return My.DataAccessMode.Insert
            End If

            Return ViewState("DataAccessMode")
        End Get
        Set(ByVal value As My.DataAccessMode)
            ViewState("DataAccessMode") = value
        End Set
    End Property

#Region "取可服務時間"
    Public Function WorkTime() As String(,)
        Dim SetTime(3, 7) As String
        For i As Integer = 0 To 2
            SetTime(i, 0) = chkSet(i, "Sunday")
            SetTime(i, 1) = chkSet(i, "Monday")
            SetTime(i, 2) = chkSet(i, "Tuesday")
            SetTime(i, 3) = chkSet(i, "Wednesday")
            SetTime(i, 4) = chkSet(i, "Thursday")
            SetTime(i, 5) = chkSet(i, "Friday")
            SetTime(i, 6) = chkSet(i, "Saturday")
        Next
        Return SetTime
    End Function

    Protected Function chkSet(ByVal index As Integer, ByVal WorkTime As String) As String
        Dim gv As GridView = TryCast(Me.fv.FindControl("gvAvailableWorkPeriod"), GridView)
        Dim chkWorkTime As CheckBox = TryCast(gv.Rows(index).FindControl(String.Format("chk{0}", WorkTime)), CheckBox)
        If (chkWorkTime.Checked) Then
            Return "Y"
        Else
            Return "N"
        End If
    End Function
#End Region

    Public Event Loaded(ByVal source As Object, ByVal e As EventArgs)
    Public Event Saved(ByVal source As Object, ByVal e As EventArgs)

End Class